/**
 * 
 */
package com.orange.modules.dao.expert;

import java.sql.SQLException;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.orange.dbhelp.DbHelper;
import com.orange.modules.entity.AuthItem;
import com.orange.modules.entity.expert.AuthExpertItemData;
import com.orange.modules.entity.expert.AuthExpertItemMap;

/**
 * @author gaogs
 *
 * 2016-12-13
 */
@Repository
public class AuthExpertItemMapRepository {
	
	@Resource(name = "dbHelper")
	private DbHelper dbHelper;
	
	@Autowired
	private AuthExpertItemMapDao authItemExpertMapDao;
	
	/**
	 * 材料评审-保存专家与一级指标的关系
	 * @param list
	 */
	public void saveAuthExpertItemMapList(List<AuthExpertItemMap> list){
		authItemExpertMapDao.save(list);
	}
	
	/**
	 * 材料评审——根据专家id删除专家与一级指标的关系
	 * @param expertId
	 * @throws SQLException 
	 */
	public void deleteAuthExpertItemMapByExpertId(String expertId,String schoolId) throws SQLException{
		String sql="DELETE FROM t_auth_item_expert_map WHERE s_expert_id=? AND s_school_id=?";
		dbHelper.execute(sql, expertId,schoolId);
	}

	
	/**
	 * 材料评审——根据专家id查询专家绑定的一级指标
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public List<AuthExpertItemMap> queryAuthExpertItemMapListByExpertId(String expertId,String schoolId) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_school_id as schoolId ,a.s_expert_id as expertId ," +
				" a.s_group_id as groupId ,a.s_item_id as itemId ,a.s_state as state ," +
				" a.s_create_time as createTime " +
				" FROM t_auth_item_expert_map as a WHERE a.s_expert_id =? AND a.s_school_id=?";
		List<AuthExpertItemMap> list=dbHelper.getBeanList(sql, AuthExpertItemMap.class, expertId,schoolId);
		return list;
	}
	
	/**
	 * 材料评审——根据专家id查询专家绑定的一级指标
	 * @param expertId
	 * @return
	 * @throws SQLException 
	 */
	public AuthExpertItemMap queryAuthExpertItemMapListByExpertId1(String expertId,String schoolId) throws SQLException{
		String sql="SELECT a.p_id as pid ,a.s_school_id as schoolId ,a.s_expert_id as expertId ," +
				" a.s_group_id as groupId ,a.s_item_id as itemId ,a.s_state as state ," +
				" a.s_create_time as createTime " +
				" FROM t_auth_item_expert_map as a WHERE a.s_expert_id =? AND a.s_school_id=?";
		AuthExpertItemMap list=dbHelper.getBean(sql, AuthExpertItemMap.class, expertId,schoolId);
		return list;
	}
	
	/**
	 * 材料评审--根据专家id查询专家组id
	 */
	public AuthExpertItemMap queryAuthExpertGroupByExpertId(String expertId) throws SQLException{
		String sql = "SELECT a.p_id as pId, a.s_expert_id as expertId, a.s_group_id as groupId, "+
				 " a.s_item_id as itemId, a.s_school_id as schoolId, "+
				 " a.s_state as state,a.s_create_time AS createTime "+
				 " FROM t_auth_item_expert_map a WHERE a.s_expert_id = ?";
		AuthExpertItemMap ae = dbHelper.getBean(sql, AuthExpertItemMap.class, expertId);
		return ae;
	}
	
	/**
	 * 材料评审--根据专家组id查询绑定的学校机构
	 */
	public List<AuthExpertItemMap> queryAuthSchoolGroupByGroupId(String groupId) throws SQLException{
		String sql = "SELECT a.p_id as pId, a.s_expert_id as expertId, a.s_group_id as groupId, "+ 
				 " a.s_item_id as itemId, a.s_school_id as schoolId, "+ 
				 " a.s_state as state,a.s_create_time AS createTime "+
				 " FROM t_auth_item_expert_map a where a.s_group_id = ?";
		 List<AuthExpertItemMap> ae = dbHelper.getBeanList(sql, AuthExpertItemMap.class, groupId);
		return ae;
	}
	
	/**
	 * 材料评审--根据专家组id和学校id查询到绑定的一级指标
	 */
	public AuthExpertItemMap queryAuthItemSchoolByGroupIdAndSchoolId(String groupId, String schoolId) throws SQLException{
		String sql ="SELECT a.p_id as pId, a.s_expert_id as expertId, a.s_group_id as groupId, "+
				 " a.s_item_id as itemId, a.s_school_id as schoolId, "+
				 " a.s_state as state,a.s_create_time AS createTime "+
				 " FROM t_auth_item_expert_map a WHERE a.s_group_id = ? AND a.s_school_id = ? ";
		AuthExpertItemMap ae = dbHelper.getBean(sql, AuthExpertItemMap.class, groupId,schoolId);
		return ae;
	}
	
	/**
	 * 材料评审--根据学校id和一级指标id查询到绑定的专家
	 */
	public AuthExpertItemMap queryAuthExpertByItemIdAndSchoolId(String schoolId, String itemId) throws SQLException{
		String sql = "SELECT a.p_id as pId, a.s_expert_id as expertId, a.s_group_id as groupId, "+
				 " a.s_item_id as itemId, a.s_school_id as schoolId, "+
				 " a.s_state as state,a.s_create_time AS createTime "+
				 " FROM t_auth_item_expert_map a WHERE a.s_item_id = ? AND a.s_school_id = ? ";
		AuthExpertItemMap ae = dbHelper.getBean(sql, AuthExpertItemMap.class, itemId,schoolId);
		return ae;
	}
	
	/**
	 * 材料评审--根据学校id查询所属学校绑定的指标
	 */
	public AuthExpertItemMap queryitemByschoolId(String schoolId) throws SQLException{
		String sql ="SELECT a.p_id as pId, a.s_expert_id as expertId, a.s_group_id as groupId, "+
				 " a.s_item_id as itemId, a.s_school_id as schoolId, "+
				 " a.s_state as state,a.s_create_time AS createTime, a.s_review as review"+
				 " FROM t_auth_item_expert_map a WHERE a.s_school_id = ? ";
		AuthExpertItemMap ae = dbHelper.getBean(sql, AuthExpertItemMap.class, schoolId);
		return ae;
	}
	
	/**
	 * 材料评审-- 查询材料评审分项意见记录表填写状态 
	 * @throws SQLException 
	 */
	public AuthExpertItemMap queryState(String schoolId, String itemId, String expertId) throws SQLException{
		String sql = "select a.p_id as pid, a.s_school_id as schoolId, a.s_item_id as itemId, a.s_expert_id as expertId, s_group_id as groupId, a.s_state as state, a.s_create_time as createTime " +
				"from t_auth_item_expert_map a WHERE a.s_school_id = ? AND a.s_item_id = ? AND a.s_expert_id = ?";
		return dbHelper.getBean(sql, AuthExpertItemMap.class, schoolId,itemId, expertId);
	}
	
	
	/**
	 * 材料评审-- 查询材料评审分项意见记录表
	 * @throws SQLException 
	 */
	public AuthExpertItemData queryMaterialInfoByTwo(String schoolId, String itemId, String expertId) throws SQLException{
		String sql = "select a.p_id as pid, a.s_school_id as schoolId, a.s_item_id as itemId, a.s_expert_id as expertId, a.s_group_id as groupId, a.s_type as type, a.s_index_id as indexId," +
				" a.s_value as value, a.s_main_problem as mainProblem, a.s_create_time as createTime, a.s_review as review from t_auth_item_expert_data as a left join t_auth_item as b on a.s_item_id = b.p_id where a.s_school_id = ? and a.s_index_id = ? and a.s_expert_id = ?";
		return dbHelper.getBean(sql, AuthExpertItemData.class, schoolId, itemId, expertId);
		
	}
	
	/**
	 * 材料评审--更改填写状态
	 */
	public void saveAuthState(AuthExpertItemMap ae){
		authItemExpertMapDao.save(ae);
	}
	
	/**
	 * 材料评审--根据专家id查询id
	 * @throws SQLException 
	 */
	public AuthExpertItemMap queryGroupIdByExpertId(String expertId) throws SQLException{
		String sql = "SELECT DISTINCT b.p_id as groupId from t_auth_group as b LEFT JOIN t_auth_item_expert_map as a on a.s_group_id = b.p_id where b.s_type = 0 and b.s_state != 3 AND a.s_expert_id = ?";
		return dbHelper.getBean(sql, AuthExpertItemMap.class, expertId);
		
	}
	
	/**
	 * 材料评审--根据专家id查询id
	 * @throws SQLException 
	 */
	public AuthExpertItemMap queryGroupIdByExpertId1(String expertId) throws SQLException{
		String sql = "SELECT b.p_id as groupId from t_auth_export_group_map as a LEFT JOIN t_auth_group as b on a.s_group_id = b.p_id where b.s_state != 3 AND a.s_type = 0 AND a.s_export_id = ?";
		return dbHelper.getBean(sql, AuthExpertItemMap.class, expertId);
		
	}
	
}
